PROC SQL queries against non SAS data sources that trigger implicit pass-through and contain a WHERE or HAVING clause might return an incorrect number of rows. Incorrect results have been verified against SAS/ACCESS^® Interface to Oracle and SAS/ACCESS^® Interface to ODBC libraries, but an incorrect number of rows could potentially be returned with other SAS/ACCESS engines.

See the Full Code tab for example code that generates an incorrect result set.

There are two workarounds. You can use either the LIBNAME option DIRECT_SQL=NOGENSQL or use the PROC SQL NOIPASSTHRU option. Use of either option produces a correct result set.

A hot fix is planned for this issue.

Operating System and Release Information

libname oralib oracle path=abc user=xxxx pass=yyyy preserve_names=yes CONNECTION = GLOBAL INSERTBUFF = 15000 DBCOMMIT = 0 READBUFF = 
15000 UPDATEBUFF = 15000 DIRECT_SQL = YES DB_LENGTH_SEMANTICS_BYTE = NO SQL_FUNCTIONS = ALL DIRECT_EXE = DELETE DBMAX_TEXT=6000;

PROC SQL;
CREATE TABLE WORK.ABC AS 
SELECT 
	t1.XXXX, 
	t1.YYYY, 
	(SUM(t1.ZZZZ)) FORMAT=COMMAX20.2 AS ZZZZ
FROM (
	SELECT 
		t1.XXXX, 
 		t1.YYYY, 
        t2.aaa,
		(SUM(t1.ZZZZ)) FORMAT=COMMAX20.2 AS ZZZZ
    FROM oralib.table1 t1
    INNER JOIN oralib.table2 t2 ON (t1.join_col = t2.join_col)
	WHERE t2.bbb = 'Z1'
	GROUP BY t1.XXXX, t1.YYYY , aaa  
) t1
GROUP BY t1.XXXX, t1.YYYY
HAVING (CALCULATED ZZZZ) > 0 ;
QUIT;